/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package at.fhj.itm.metadata.impl.postgres;


import at.fhj.itm.metadata.Constraint;
import at.fhj.itm.metadata.ForeignKeyConstraint;
import at.fhj.itm.metadata.MetaDataException;
import at.fhj.itm.metadata.MetadataProvider;
import at.fhj.itm.metadata.Schema;
import at.fhj.itm.metadata.SqlType;
import at.fhj.itm.metadata.TriggerFlag;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.EnumSet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;


/**
 *
 * @author Seuchter
 */
public class PostgresMetadataProvider implements MetadataProvider {

    private Connection connection;


    private static final Map<String, String> FOREIGN_KEY_ACTION_MAPPING;
    private static final Map<String, String> CONSTRAINT_TYPE_MAPPING;
    private static final Map<Integer, TriggerFlag> TRIGGER_FLAGS_MAPPING;

    static{
        FOREIGN_KEY_ACTION_MAPPING = new HashMap<String, String>();
        FOREIGN_KEY_ACTION_MAPPING.put("a", ForeignKeyConstraint.CONSTRAINT_ACTION_NONE);
        FOREIGN_KEY_ACTION_MAPPING.put("r", ForeignKeyConstraint.CONSTRAINT_ACTION_RESTRICT);
        FOREIGN_KEY_ACTION_MAPPING.put("c", ForeignKeyConstraint.CONSTRAINT_ACTION_CASCADE);
        FOREIGN_KEY_ACTION_MAPPING.put("n", ForeignKeyConstraint.CONSTRAINT_ACTION_SET_NULL);
        FOREIGN_KEY_ACTION_MAPPING.put("d", ForeignKeyConstraint.CONSTRAINT_ACTION_SET_NULL);

        CONSTRAINT_TYPE_MAPPING = new HashMap<String, String>();
        CONSTRAINT_TYPE_MAPPING.put("f", Constraint.CONSTRAINT_TYPE_FOREIGN);
        CONSTRAINT_TYPE_MAPPING.put("c", Constraint.CONSTRAINT_TYPE_CHECK);
        CONSTRAINT_TYPE_MAPPING.put("p", Constraint.CONSTRAINT_TYPE_PRIMARY_KEY);
        CONSTRAINT_TYPE_MAPPING.put("u", Constraint.CONSTRAINT_TYPE_UNIQUE);


        TRIGGER_FLAGS_MAPPING = new HashMap<Integer, TriggerFlag>();
        TRIGGER_FLAGS_MAPPING.put(1, TriggerFlag.ROW);
        TRIGGER_FLAGS_MAPPING.put(1<<1, TriggerFlag.BEFORE);
        TRIGGER_FLAGS_MAPPING.put(1<<2, TriggerFlag.INSERT);
        TRIGGER_FLAGS_MAPPING.put(1<<3, TriggerFlag.DELETE);
        TRIGGER_FLAGS_MAPPING.put(1<<4, TriggerFlag.UPDATE);
    }
    
    public List<PostgresSequence> getSequenceFromNames(final List<String> sequenceNames) throws SQLException
    {
        List<PostgresSequence> sequences = new ArrayList<PostgresSequence>();
        Statement stmt = connection.createStatement();
        final String queryTemplate = MetadataQueries.getQuery("querySequence");
        for(String curName : sequenceNames)
        {
        	String query = String.format(queryTemplate, PostgresJdbcUtil.quoteDatabaseItem(curName));
        	ResultSet set = stmt.executeQuery(query);
            set.next();
            long minSize = set.getLong("min_value");
            long maxSize = set.getLong("max_value");
            long startValue = set.getLong("start_value");
            long step = set.getLong("increment_by");
            boolean isCylce = set.getBoolean("is_cycled");
            long curValue = set.getLong("last_value");
            
            PostgresSequence seq = new PostgresSequence(curName, minSize, maxSize,
                    step, startValue,isCylce, -1);

            sequences.add(seq);

            set.close();
        }
        stmt.close();
        return sequences;
    }

    protected Connection getConnection(){
        return connection;
    }

    public List<PostgresSequence> getSequences(long schemaOid)
    {
        List<String> sequenceNAmes = new ArrayList<String>();

        try
        {
            String stmtStr = MetadataQueries.
                    getQuery("sequenceNamesBySchema");
            PreparedStatement stmt = connection.prepareStatement(stmtStr);

            stmt.setLong(1, schemaOid);
            ResultSet set = stmt.executeQuery();
            while(set.next())
            {
                String sequenceName = set.getString("relname");
                sequenceNAmes.add(sequenceName);
            }
            set.close();
            stmt.close();

            return getSequenceFromNames(sequenceNAmes);
        }
        catch(Exception ex)
        {
            throw new MetaDataException("Error retrieving sequences",ex);
        }


        
    }

    public PostgresProcedure getProcedure(long oid)
    {
        try
        {
            PreparedStatement stmt = connection.prepareStatement(MetadataQueries.getQuery("procedureById"));
            stmt.setLong(1, oid);
            ResultSet set = stmt.executeQuery();

            String name = set.getString("proname");
            String src= set.getString("prosrc");

            //PostgresProcedure proc = new PostgresProcedure(name, src, oid);
            return null;
            //return proc;

        }
        catch(SQLException ex)
        {
            throw new MetaDataException("Error retrieving procedure.", ex);
        }
    }
    private EnumSet<TriggerFlag> getTriggerFlags(int flags)
    {
        Set<Integer> knownFlags = TRIGGER_FLAGS_MAPPING.keySet();
        EnumSet<TriggerFlag> triggerFlags = EnumSet.noneOf(TriggerFlag.class);
        for(int curFlag : knownFlags)
        {
            if((flags & curFlag) > 0)
            {
                triggerFlags.add(TRIGGER_FLAGS_MAPPING.get(curFlag));
            }
        }

        return triggerFlags;

    }

    private List<PostgresProcedureArgument> createProcedureArguments(Long[] typeOids, String[] argNames){
        if(typeOids.length != argNames.length){
            throw new IllegalArgumentException("type and name array are of unequal length");
        }

        List<PostgresProcedureArgument> args = new ArrayList<PostgresProcedureArgument>();

        for(int i = 0; i < typeOids.length; i++){
            long curTypeOid = typeOids[i];
            String curName = argNames[i];
            PostgresSqlDataType dataType = getDataTypeFromOid(curTypeOid, -1);
            args.add(new PostgresProcedureArgument(i, curName, dataType));
        }


        return args;

    }
    public List<PostgresProcedure> getProceduresInSchema(long schemaOid){
        try{
            PreparedStatement stmt = connection.prepareStatement(MetadataQueries.getQuery("procedureBySchema"));
            stmt.setLong(1, schemaOid);
            List<PostgresProcedure> procs = new ArrayList<PostgresProcedure>();
            ResultSet set = stmt.executeQuery();
            //SELECT proname, proowner, prolang, pronargs, prorettype, proargtypes,
            //proargnames, lanname FROM pg_proc INNER JOIN pg_language l ON prolang = l.oid
            //WHERE pronamespace = 2200;
            while(set.next()){
                long oid = set.getLong("oid");
                String procName = set.getString("proname");
                String procLang = set.getString("lanname");
                int argsCount = set.getInt("pronargs");
                long procRetTypeOid = set.getLong("prorettype");
                Long [] types =(Long[]) set.getArray("proallargtypes").getArray();
                String definition = set.getString("prodef");
                String [] argNames = (String[]) set.getArray("proargnames").getArray();
                List<PostgresProcedureArgument> procArgs = createProcedureArguments(types, argNames);
                PostgresSqlDataType returnType = getDataTypeFromOid(procRetTypeOid, -1);

                PostgresProcedure proc = new PostgresProcedure(procName, definition, procArgs, returnType,procLang ,oid);
                procs.add(proc);
            }


            set.close();
            stmt.close();
            return procs;
        }
        catch(SQLException ex){
            throw new MetaDataException("Error retrieving proecdures", ex);
        }
    }
    public List<PostgresTrigger> getTriggers(long tableOid)
    {
        try
        {
            List<PostgresTrigger> triggers = new ArrayList<PostgresTrigger>();
            PreparedStatement stmt = connection.prepareStatement(MetadataQueries.getQuery("triggerById"));

            stmt.setLong(1,tableOid);

            ResultSet res = stmt.executeQuery();


            while(res.next())
            {
                long oid = res.getLong("oid");
                int tgflags = res.getInt("tgattr");
                String name = res.getString("tgname");
                long procOid= res.getLong("tgfoid");
                PostgresProcedure proc = getProcedure(procOid);

                PostgresTrigger trigger = new PostgresTrigger(name, null, proc, oid);

            }
            res.close();
            stmt.close();


            return triggers;
        }
        catch(Exception ex)
        {
            throw new MetaDataException("Error retrieving triggers", ex);
        }
    }

    public PostgresMetadataProvider(Connection connection)
    {
        this.connection = connection;
    }

    public List<PostgresSchema> getSchema() throws MetaDataException
    {
        try
        {
            List<PostgresSchema> schemas = new ArrayList<PostgresSchema>();

            String queryStr =  MetadataQueries.getQuery("allSchemas");

            PreparedStatement schemaStmt = connection.prepareStatement(queryStr);

            ResultSet schemaResult = schemaStmt.executeQuery();
            Statement changeSchemaStmt = connection.createStatement();
            while(schemaResult.next())
            {
                long oid = schemaResult.getLong("oid");
                String name = schemaResult.getString("nspname");
                PostgresSchema schema = new PostgresSchema(oid, name);
                changeSchemaStmt.execute(String.format("SET search_path TO \"%s\"", name));
                List<PostgresSequence> sequences = getSequences(oid);
                List<PostgresTable> tables = getTablesInSchema(schema.getOid());
                List<PostgresProcedure> procs = getProceduresInSchema(oid);
                Collections.sort(tables);
                addConstraints(tables);
                schema.setSequences(sequences);
                schema.setTables(tables);
                schema.setProcedures(procs);

                schemas.add(schema);

            }
            schemaResult.close();
            schemaStmt.close();
            return schemas;

        }
        catch(SQLException e)
        {
            throw new MetaDataException("Error while fetching schema meta-data", e);
        }
    }

    public List<PostgresTable> getTablesInSchema(long schemaOid) throws  MetaDataException
    {
        try
        {
            List<PostgresTable> tables = new ArrayList<PostgresTable>();
            PreparedStatement tableStmt = connection.prepareStatement(MetadataQueries.getQuery("tablesInSchema"));
            tableStmt.setLong(1, schemaOid);
            ResultSet tableResult = tableStmt.executeQuery();

            while(tableResult.next())
            {
                long oid = tableResult.getLong("oid");
                String name = tableResult.getString("relname");
                long ownerOid = tableResult.getLong("relowner");
                PostgresUser user = getUserFromOid(ownerOid);
                List<PostgresTrigger> triggers = getTriggers(oid);
                PostgresTable table = new PostgresTable(oid, name, user);

                table.setColumns(getColumnsInTable(oid));

                List<PostgresIndex> indices = getIndicesForTable(table);
                table.setIndices(indices);
                table.setTriggers(triggers);
                tables.add(table);

            }

            return tables;

        }
        catch(SQLException e)
        {
            throw new MetaDataException("Error while fetching table meta-data", e);
        }
    }
    private PostgresSqlDataType createDataType(String name, int typeLen, boolean isArray, long oid, long typemod)
    {
        if(oid == TypeOids.NUMERIC)
        {
            return new PostgresNumeric(name, typeLen, isArray,oid, typemod);
        }
        if(oid == TypeOids.VARCHAR || oid == TypeOids.VARBIT)
        {
            return new PostgresVariableLengthSqlType(name, typeLen, isArray, (int) (typemod - 4), oid);
        }


        return new PostgresSqlDataType(name, typeLen, isArray, oid);

    }
    public PostgresSqlDataType getDataTypeFromOid(long oid, long typmod) throws MetaDataException
    {
        try
        {
            PreparedStatement typeStatement = connection.prepareStatement(MetadataQueries.getQuery("typeById"));
            typeStatement.setLong(1, oid);
            ResultSet columnResult = typeStatement.executeQuery();
            columnResult.next();

            String name = columnResult.getString("typname");
            int typeLen = columnResult.getInt("typlen");
            boolean typeNotNull= columnResult.getBoolean("typnotnull");
            long arrayTypeoid = columnResult.getLong("typarray");
            PostgresSqlDataType dataType = createDataType(name, typeLen, arrayTypeoid == 0, oid, typmod);
            columnResult.close();
            typeStatement.close();
            return dataType;
        }
        catch(SQLException ex)
        {
            throw new MetaDataException("Error retrieving type for column",ex);
        }
    }
    public PostgresUser getUserFromOid(long oid)throws MetaDataException
    {
        try{

            PreparedStatement stmt = connection.prepareCall(MetadataQueries.getQuery("userById"));

            stmt.setLong(1, oid);

            ResultSet userResult = stmt.executeQuery();

            boolean found = userResult.next();
            if(!found){
                userResult.close();
                stmt.close();
                throw new MetaDataException("Unable to retrieve user with with oid " + oid,null);
            }


            long userOid = userResult.getLong("oid");
            String name = userResult.getString("rolname");

            PostgresUser user = new PostgresUser(userOid, name);

            userResult.close();

            stmt.close();
            return user;

        }
        catch(SQLException ex){
            throw new MetaDataException("Error retrieving sql user", ex);
        }
    }
    private List<PostgresColumn> getColumnsFromIndices(PostgresTable table, Integer[] indices) throws MetaDataException
    {
        try
        {

            List<PostgresColumn> columns = new ArrayList<PostgresColumn>();

            for(int curInd : indices)
            {
                int colIndex = curInd -1;

                if(colIndex < 0 || colIndex >= table.getColumns().size())
                {
                    throw new MetaDataException("Error mapping column index to previously fetched column.");
                }
                PostgresColumn col = table.getColumns().get(colIndex);
                columns.add(col);
            }

            return columns;



        }
        catch(Exception ex)
        {
            throw new MetaDataException("Error retrieving index metadata.", ex);

        }
    }
    public List<PostgresIndex> getIndicesForTable(PostgresTable table) throws MetaDataException
    {
        try
        {
            String queryStr = MetadataQueries.getQuery("indicesByTable");
            PreparedStatement stmt = connection.prepareStatement(queryStr);
            stmt.setLong(1,table.getOid());

            ResultSet indicesResult = stmt.executeQuery();
            List<PostgresIndex> indices = new ArrayList<PostgresIndex>();

            while(indicesResult.next())
            {
                boolean isUnqiue = indicesResult.getBoolean("indisunique");
                boolean isClustered = indicesResult.getBoolean("indisclustered");
                boolean isPrimary = indicesResult.getBoolean("indisprimary");
                String name = indicesResult.getString("relname");
                String accessMethod = indicesResult.getString("amname");
                Integer[] ind =  PostgresJdbcUtil.getInt2Vector(indicesResult, "indkey");



                List<PostgresColumn> columns = getColumnsFromIndices(table, ind);

                PostgresIndex index = new PostgresIndex(name, isPrimary, isUnqiue, isClustered, 
                        accessMethod, columns, table);
                indices.add(index);
            }

            indicesResult.close();
            stmt.close();
            return indices;
        }
        catch(SQLException ex)
        {
            throw  new MetaDataException("Error retrieving indices from table");
        }
        
    }
    public void addConstraints(List<PostgresTable> tables) throws MetaDataException
    {
        try
        {
            for(PostgresTable curTable : tables)
            {
                addConstraintsForTable(curTable, tables);
            }
        }
        catch(SQLException ex){
            throw new MetaDataException("Error retrieving constraints", ex);
        }

    }
    private PostgresTable findTableByOid(List<PostgresTable> tables, long oid)
    {
        for(PostgresTable t : tables)
        {
            if(t.getOid() == oid)
            {
                return t;
            }
        }

        return null;
    }
    private PostgresForeignKey createForeignKeyConstraint(ResultSet data, PostgresTable curTable, List<PostgresTable> tables)
            throws SQLException, MetaDataException
    {
        String name = data.getString("conname");
        boolean deferrable = data.getBoolean("condeferrable");
        boolean defererred = data.getBoolean("condeferred");

        long oid = data.getLong("oid");
        long referencedTableOid = data.getLong("confrelid");
        PostgresTable refTable = findTableByOid(tables, referencedTableOid);
        Integer[] refIndices = (Integer[]) data.getArray("confkey").getArray();
        Integer[] constraintIndicies = (Integer[]) data.getArray("conkey").getArray();
        List<PostgresColumn> refColumns = getColumnsFromIndices(refTable, refIndices);
        List<PostgresColumn> constraintColumns = getColumnsFromIndices(curTable, constraintIndicies);
        for(PostgresColumn c : constraintColumns){
        	c.setForeignKey(true);
        }
        String updateType = data.getString("confupdtype");
        String deleteType = data.getString("confdeltype");

        String deleteAction = FOREIGN_KEY_ACTION_MAPPING.get(deleteType);
        String updateAction = FOREIGN_KEY_ACTION_MAPPING.get(updateType);

        PostgresForeignKey key =  new PostgresForeignKey(oid, name, updateAction,
                deleteAction, deferrable, defererred, refColumns, constraintColumns, refTable);

        return key;

    }
    private PostgresConstraint createSimpleConstraint(ResultSet data, PostgresTable curTable, 
            List<PostgresTable> tables, String constraintType)
            throws SQLException, MetaDataException
    {
        String name = data.getString("conname");

        long oid = data.getLong("oid");
        long referencedTableOid = data.getLong("confrelid");
        PostgresTable refTable = findTableByOid(tables, referencedTableOid);

        Integer[] constraintIndicies = (Integer[]) data.getArray("conkey").getArray();

        List<PostgresColumn> constraintColumns = getColumnsFromIndices(curTable, constraintIndicies);
        for(PostgresColumn col : constraintColumns){
        	if(constraintType.equals(Constraint.CONSTRAINT_TYPE_PRIMARY_KEY)){
        		col.setPrimarKey(true);
        	}
        }
   

        PostgresConstraint c = new PostgresConstraint(oid, name, constraintColumns, constraintType);

        return c;
    }
     private PostgresCheckConstraint createCheckConstraint(ResultSet data, PostgresTable curTable, List<PostgresTable> tables)
            throws SQLException, MetaDataException
    {
        String name = data.getString("conname");

        long oid = data.getLong("oid");
        long referencedTableOid = data.getLong("confrelid");
        PostgresTable refTable = findTableByOid(tables, referencedTableOid);

        Integer[] constraintIndicies = (Integer[]) data.getArray("conkey").getArray();

        List<PostgresColumn> constraintColumns = getColumnsFromIndices(curTable, constraintIndicies);

        String checkExpr = data.getString("checkdef");
        

        PostgresCheckConstraint c = new PostgresCheckConstraint(oid, name, constraintColumns, checkExpr);

        return c;
    }
    public void addConstraintsForTable(PostgresTable table, List<PostgresTable> tables) throws SQLException,MetaDataException
    {
       List<PostgresConstraint> constraints = new ArrayList<PostgresConstraint>();

       PreparedStatement stmt = connection.prepareStatement(MetadataQueries.getQuery("constraintsByTable"));
       stmt.setLong(1, table.getOid());
       ResultSet constraintSet = stmt.executeQuery();
       while(constraintSet.next()){
           String type = constraintSet.getString("contype");

           if(type.equals("f"))
           {
                PostgresForeignKey fk = createForeignKeyConstraint(constraintSet, table, tables);
                constraints.add(fk);
                
           }
           else if (type.equals("c"))
           {
                PostgresCheckConstraint cc = createCheckConstraint(constraintSet, table, tables);
                constraints.add(cc);
           }
           else if(type.equals("u") || type.equals("p"))
           {
                PostgresConstraint c = createSimpleConstraint(constraintSet, table, tables, CONSTRAINT_TYPE_MAPPING.get(type));
                constraints.add(c);
           }


       }
       constraintSet.close();

       stmt.close();


       table.setConstraint(constraints);
    }
    public List<PostgresColumn> getColumnsInTable(long relid) throws MetaDataException
    {
        try
        {
            List<PostgresColumn> columns = new ArrayList<PostgresColumn>();
            PreparedStatement columnStatement = connection.prepareStatement(MetadataQueries.getQuery("columnInTable"));
            columnStatement.setLong(1, relid);
            ResultSet columnResult = columnStatement.executeQuery();

            while(columnResult.next())
            {
                String colName = columnResult.getString("attname");
                int attlen = columnResult.getInt("attlen");
                int attnum = columnResult.getInt("attnum");
                boolean isNotNull = columnResult.getBoolean("attnotnull");
                long typeOid = columnResult.getLong("atttypid");
                long typmod = columnResult.getLong("atttypmod");
                String defaultValue = columnResult.getString("adsrc");
                SqlType type = getDataTypeFromOid(typeOid,typmod);
                PostgresColumn column = new PostgresColumn(attnum, colName, isNotNull,type, defaultValue);
                columns.add(column);

            }
            columnResult.close();
            columnStatement.close();
            return columns;

        }
        catch(SQLException ex)
        {
            throw new MetaDataException("Error while fetching column meta-data", ex);
        }
    }

}
